package com.qipay.mapper;

public class CpSqlProvider extends com.qipay.baas.mapper.CpSqlProvider {

    public String allCpList(String name,Long cpId,String contact,String phone,Byte state,Long agentId) {
        String sql = "SELECT t.account_state account_state,t.id cp_id,t1.name name,t.agent_id agent_id,IFNULL(t3.total,0)total," +
                "IFNULL(t3.fee,0)fee," +
                "IFNULL(t4.drawAmount,0)widthdraw_amount from cp t " +
                "LEFT JOIN cp_info t1 ON t.id=t1.cp_id " +
                "LEFT JOIN (select cp_id,SUM(actual_amount)total,SUM(fee)fee ,SUM(agent_fee)agent_fee " +
                "from `order` where state=3 GROUP BY cp_id)t3 ON t.id=t3.cp_id " +
                "LEFT JOIN (select cp_id,SUM(amount)drawAmount " +
                "FROM cp_withdraw_tradlog where state in(0,2) GROUP BY cp_id)t4 ON t.id=t4.cp_id " +
                "where 1=1";
//        String typeStr = " t.type="+type;
//        String nameStr = name==""?"":" and t1.name="+name;
//        String personStr = person==""?"":" and t1.contacts="+person;
//        String phoneStr = phone==""?"":" and t1.contacts_tel="+phone;
//        String cpIdStr = cpId==null?"":" and t.id="+cpId;
//        String agentIdStr = agentId==null?"":" and t.agent_id="+cpId;
//
//        return String.format(sql,typeStr,nameStr,personStr,phoneStr,cpIdStr,agentIdStr);


        StringBuffer stringBuffer = new StringBuffer(sql);
        if (state!=null&&state!=-1){
            stringBuffer.append(" and t.account_state="+state);
        }
        if (!name.isEmpty()){
            stringBuffer.append(" and t1.name like "+"'%"+name+"%'");
        }
        if (!contact.isEmpty()){
            stringBuffer.append(" and t1.contacts = "+"'"+contact+"'");
        }
        if (!phone.isEmpty()){
            stringBuffer.append(" and t1.contacts_tel = "+"'"+phone+"'");
        }
        if (cpId!=null){
            stringBuffer.append(" and t.id = "+cpId);
        }
        if (agentId!=null){
            stringBuffer.append(" and t.agent_id = "+agentId);
        }
        return stringBuffer.toString();
    }

    public String allAgentList(String name,Long cpId,String person,String phone,Byte type,Long agentId) {
        String sql = "SELECT t.account_state account_state,t.id cp_id,t1.name name,t.agent_id agent_id," +
                "IFNULL(t3.fee,0)fee,IFNULL(t3.agent_fee,0)agent_fee,IFNULL(t4.withdraw_fee,0)withdraw_fee," +
                "IFNULL(t4.drawAmount,0)widthdraw_amount from cp t " +
                "LEFT JOIN cp_info t1 ON t.id=t1.cp_id " +
                "LEFT JOIN (select agent_id,SUM(fee)fee ,SUM(agent_fee)agent_fee  " +
                "from `order` where state=3 GROUP BY agent_id)t3 ON t.id=t3.agent_id " +
                "LEFT JOIN (select cp_id,SUM(amount)drawAmount,SUM(fee)withdraw_fee " +
                "FROM cp_withdraw_tradlog where state in(0,2) GROUP BY cp_id)t4 ON t.id=t4.cp_id " +
                "where %s %s";
        String typeStr = "t.type="+type;
        String cpIdStr = cpId==null?"":"and t.id="+cpId;

        return String.format(sql, typeStr, cpIdStr);
    }



}